// t_article

// 导入数据库操作
const { sqlOne, sqlBatch } = require('../db/common')
const redisClient = require('../utils/redis-client');

// const redisClient = new RedisClient();

// 成功响应 onSuccess 失败响应 onError

// 文章列表 -- 后续加分类查询
exports.getArticlePage = async (req, res) => {
    try {

        // const { pageSize, pageNum,desc,type } = req.query
        // const {category} = req.body
        // const startNum = pageSize * (pageNum - 1)
        // let sql = `SELECT ta.id,tu.username as username,ta.title as title,ta.createdAt,ta.cover 
        // FROM t_article as ta,t_user as tu where ta.user_id = tu.id 
        // and ta.status=1 ORDER BY view_count limit ?,?;
        // `;
        // sql += 'select count(id) as total FROM t_article where status=1;'
        // const [results, count] = await sqlBatch(sql, [startNum, pageSize])
        // res.onSuccess({ data: results, total: count[0].total })



        // 文章详情信息 desc 正序倒序  type 排序类型 1热度/2时间  category分类
        const { pageSize, pageNum, desc, type, keywords } = req.body;
        const { category } = req.body;
        const startNum = pageSize * (pageNum - 1);
        const redisKey = `article:${pageSize + pageNum + desc + type + keywords + category.join(',')}`
        // 1.先redis 
        const articleFromCache = await redisClient.get(redisKey);
        if (articleFromCache) {
            return res.onSuccess(JSON.parse(articleFromCache))
        }

        // 先去除两侧空格 在分割
        const keywordsArr = String(keywords).replace(/^\s*|\s*$/g, "").split(' ')

        let keywordsSql = ` `

        keywordsSql += keywordsArr.map(item => ` ta.title like ? `).join(' or ');
        keywordsSql += ' or '
        keywordsSql += keywordsArr.map(item => ` ta.summary like ? `).join(' or ');

        // 构建排序部分的SQL  
        let orderBy;
        if (type === '1') {
            // 按热度排序  
            orderBy = `ta.view_count ${desc === '1' ? 'DESC' : 'ASC'}`;
        } else if (type === '2') {
            // 按时间排序  
            orderBy = `ta.createdAt ${desc === '1' ? 'DESC' : 'ASC'}`;
        }

        // 构建分类筛选部分的SQL  
        let categoryCondition = '';
        if (Array.isArray(category) && category.length > 0) {
            categoryCondition = `AND EXISTS (  
                SELECT 1 FROM t_cate_article   
                WHERE t_cate_article.article_id = ta.id   
                AND t_cate_article.cate_id IN (${category.map(() => '?').join(',')})  
            )`;
        }

        // 构建分页查询SQL  
        let sql = `  
            SELECT ta.id, tu.username AS username, ta.title AS title, ta.createdAt, ta.cover, ta.summary  
            FROM t_article AS ta  
            JOIN t_user AS tu ON ta.user_id = tu.id  
            WHERE ta.status = 1 and ta.deletedAt is NULL and (${keywordsSql}) 
            ${categoryCondition}  
            ORDER BY ${orderBy}  
            LIMIT ?, ?;  
        `;

        // 构建总数查询SQL  
        sql += `  
            SELECT COUNT(ta.id) AS total   
            FROM t_article AS ta  
            WHERE ta.status = 1 and ta.deletedAt is NULL  and (${keywordsSql}) 
            ${categoryCondition};  
        `;

        // 执行SQL查询，注意替换占位符  
        const totalParams = [];
        if (categoryCondition) {
            totalParams.push(...category); // 添加分类ID作为参数  
        }
        totalParams.push(...keywordsArr.map(item => `%${item}%`))
        totalParams.push(...keywordsArr.map(item => `%${item}%`))

        const [results, count] = await sqlBatch(sql, [...keywordsArr.map(item => `%${item}%`), ...keywordsArr.map(item => `%${item}%`), ...(categoryCondition ? category : []), startNum, pageSize, ...totalParams])
        const data = { data: results, total: count[0].total }
        redisClient.setWithTTL(redisKey, JSON.stringify(data), 60 ); // 一小时
        res.onSuccess(data)
    } catch (e) {
        res.onError(e)
    }
}


// 文章的详情查询
exports.getArticleDetail = async (req, res) => {

    try {
        const { id } = req.query
        let sql = `select * from t_article where id = ?`
        const results = await sqlOne(sql, [id]);
        // 1.判断文章的状态
        if (results.length == 0) {
            return res.onError("文章信息不存在");
        }
        console.log(results[0].deletedAt);
        if (results[0].status != 1 || results[0].deletedAt) {
            return res.onError("文章状态异常");
        }


        // 2.文章用户信息
        sql = `select id,username,avatar from t_user where id=?`
        const [userInfo] = await sqlOne(sql, [results[0].user_id])

        // 推荐内容


        const sqlC = `SELECT tc.name,tc.id FROM t_category as tc,t_cate_article as tca where tc.id = tca.cate_id and tca.article_id=? limit 5`;
        // 读取文章的分类
        const categoryList = await sqlOne(sqlC, id)

        // 3.增加浏览量  缓存到redis 写定时任务来更新数据库
        const cachedViews = await redisClient.get(`article:${id}:views`);
        let views = parseInt(cachedViews, 10) || 0; // 如果未命中，则默认为 0  
        // 更新 Redis 中的访问量（使用原子操作 INCR）  
        await redisClient.client.incr(`article:${id}:views`);
        views++; // 因为 INCR 是在前面执行的，所以这里需要手动加 1  

        // 添加文章 ID 到 Redis 集合  
        redisClient.client.sadd('articles:ids', id);

        results[0].view_count += (views - 1);

        res.onSuccess({ article: results[0], userInfo: userInfo, categoryList })
    } catch (e) {
        return res.onError(e);
    }
}


// 添加文章
exports.addArticle = async (req, res) => {
    try {
        // console.log(req.auth);
        const { cover, summary, title, content, category } = req.body
        const entiy = { cover, summary, title, content, status: 0, createdAt: new Date(), view_count: 0, user_id: req.auth.id }
        const sql = `insert into t_article set ?`;
        const results = await sqlOne(sql, [entiy])
        const nowDate = new Date()
        // console.log(category);
        // 设置文章的所属分类
        // 1.新增的分类  筛选出
        const newcate = category.filter(item => {
            // console.log(typeof item =='string');
            return typeof item == 'string'
        })
        let paramsArr = []
        const idArr = []
        // 创建一个插入语句的数组  
        const insertQueries = newcate.map((category, index) => {
            // 这里假设你有一个自增的ID字段和一个名为'name'的分类字段  
            paramsArr = [...paramsArr, category, 0, nowDate]
            return `INSERT INTO t_category (name,status,createdAt) VALUES (?,?,?)`;
        });
        if (insertQueries.length > 0) {
            // console.log(insertQueries);
            // 新增
            const results1 = await sqlBatch(insertQueries.join(';'), paramsArr)
            // console.log(results1);
            results1.forEach(item => idArr.push(item.insertId))
        }
        // 2.将分类存入表中
        category.forEach(item => {
            if (typeof item == 'number') {
                idArr.push(item)
            }
        })
        const insertQ = idArr.map(item => {
            return `INSERT INTO t_cate_article (cate_id,article_id) VALUES (?,${results.insertId})`
        })
        sqlBatch(insertQ.join(';'), idArr)
        // console.log(results);
        res.onSuccess()
    } catch (e) {
        res.onError(e)
    }
}

// 删除文章
exports.deleterticle = async (req, res) => {
    const sql = `update t_article set deletedAt = ? where id = ?`
    const results = await sqlOne(sql,[new Date(),req.params.id])
    res.onSuccess()
}

// 修改文章
exports.updateArticle = async (req, res) => {
    try {
        const userId = req.auth.id
        const id = req.body.id // 文章id
        console.log(id);
        // 查询该文章是否为自己的
        const flag = await articleIsMy(userId, id)
        if (flag) {
            return res.onError('文章数据不存在')
        }

        // 删除原本文章的分类
        const delSql = `delete from t_cate_article where article_id=?`
        const r = sqlOne(delSql, [id])

        // 执行修改
        // console.log(req.auth);
        const { cover, summary, title, content, category } = req.body
        const entiy = { cover, summary, title, content, status: 0, updatedAt: new Date() }
        const sql = `update t_article set ? where id =?`;
        const results = await sqlOne(sql, [entiy, id])
        const nowDate = new Date()
        // console.log(category);
        // 设置文章的所属分类
        // 1.新增的分类  筛选出
        const newcate = category.filter(item => {
            // console.log(typeof item =='string');
            return typeof item == 'string'
        })
        let paramsArr = []
        const idArr = []
        // 创建一个插入语句的数组  
        const insertQueries = newcate.map((category, index) => {
            // 这里假设你有一个自增的ID字段和一个名为'name'的分类字段  
            paramsArr = [...paramsArr, category, 0, nowDate]
            return `INSERT INTO t_category (name,status,createdAt) VALUES (?,?,?)`;
        });
        if (insertQueries.length > 0) {
            // console.log(insertQueries);
            // 新增
            const results1 = await sqlBatch(insertQueries.join(';'), paramsArr)
            // console.log(results1);
            results1.forEach(item => idArr.push(item.insertId))
        }
        // 2.将分类存入表中
        category.forEach(item => {
            if (typeof item == 'number') {
                idArr.push(item)
            }
        })
        const insertQ = idArr.map(item => {
            return `INSERT INTO t_cate_article (cate_id,article_id) VALUES (?,${id})`
        })
        sqlBatch(insertQ.join(';'), idArr)
        // console.log(results);
        res.onSuccess()
    } catch (e) {
        res.onError(e)
    }
}


// 查询文章是否属于自己
async function articleIsMy(userId, id) {
    const sql = `select id from t_article where user_id = ? and id = ?`
    const results = await sqlOne(sql, [userId, id])
    return results.length == 0;
}

// 获取个人文章详情
exports.myArticleDetail = async (req, res) => {
    const userId = req.auth.id
    const id = req.params.id
    // 查询该文章是否为自己的
    const flag = await articleIsMy(userId, id)
    if (flag) {
        return res.onError('文章数据不存在')
    }
    // 读取
    const sql = `select * from t_article where user_id = ? and id = ?`
    const results = await sqlOne(sql, [userId, id])
    // 分类
    const sqlC = `SELECT tc.id FROM t_category as tc,t_cate_article as tca where tc.id = tca.cate_id and tca.article_id=? limit 5`;
    // 读取文章的分类
    const categoryList = await sqlOne(sqlC, id)
    res.onSuccess({ article: results[0], categoryList })
}

// 自己收藏的文章
exports.myCollectArticlePage = async (req, res) => {
    try {
        const { pageSize, pageNum, desc, type } = req.query;
        const userId = req.auth.id
        const startNum = pageSize * (pageNum - 1);
        // 构建排序部分的SQL  

        // 构建分页查询SQL  
        let sql = `  
            SELECT ta.id, tu.username AS username, ta.title AS title, ta.createdAt, ta.cover, ta.summary  
            FROM t_article AS ta  
            JOIN t_user AS tu ON ta.user_id = tu.id  
            WHERE ta.status = 1  and ta.id in (select article_id from t_article_collect where user_id = ? ) LIMIT ?, ?
            ;  
        `;

        // 构建总数查询SQL  
        sql += `select count(article_id) as total from t_article_collect  where  user_id = ?;`;

        const [results, count] = await sqlBatch(sql, [userId, startNum, pageSize, userId])

        res.onSuccess({ data: results, total: count[0].total })
    } catch (e) {
        res.onError(e)
    }
}

// 用户主页 - 文章列表 
exports.getUserArticlePage = async (req, res) => {
    try {
        const { pageSize, pageNum, desc, type } = req.query;
        let { userId } = req.body

        const startNum = pageSize * (pageNum - 1);

        if (!userId) {
            userId = req.auth.id
        }

        // 构建排序部分的SQL  
        let orderBy;
        if (type === '1') {
            // 按热度排序  
            orderBy = `ta.view_count ${desc === '1' ? 'DESC' : 'ASC'}`;
        } else if (type === '2') {
            // 按时间排序  
            orderBy = `ta.createdAt ${desc === '1' ? 'DESC' : 'ASC'}`;
        }


        // 构建分页查询SQL  
        let sql = `  
            SELECT ta.id, tu.username AS username, ta.title AS title, ta.createdAt, ta.cover, ta.summary  
            FROM t_article AS ta  
            JOIN t_user AS tu ON ta.user_id = tu.id  
            WHERE ta.status = 1  and  ta.user_id=? and ta.deletedAt is NULL 
            ORDER BY ${orderBy}  
            LIMIT ?, ?;  
        `;

        // 构建总数查询SQL  
        sql += `  
            SELECT COUNT(ta.id) AS total   
            FROM t_article AS ta  
            WHERE ta.status = 1  and ta.user_id=?  and ta.deletedAt is NULL
        `;




        const [results, count] = await sqlBatch(sql, [userId, startNum, pageSize, userId])


        res.onSuccess({ data: results, total: count[0].total })
    } catch (e) {
        res.onError(e)
    }
}
